package cn.jussi.mydbutils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class MyDBUtils {
	
	private static DataSource dataSource = null;
	static {//静态代码块只执行一次
		dataSource = new ComboPooledDataSource("mssql");
	}
	
	
	public static Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}
	
	/**
	 * 开始事务处理的工具类
	 * @param conn
	 */
	public static void startTransaction(Connection conn) {
		if(conn != null) {
			try {
				conn.setAutoCommit(false);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	
	/**
	 * 提交事务处理的工具类
	 * @param conn
	 */
	public static void commitTransaction(Connection conn) {
		if(conn != null) {
			try {
				conn.commit();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}	
	}
	
	
	/**
	 * 回滚事务处理的工具类
	 * @param conn
	 */
	public static void rollbackTransaction(Connection conn) {
		if(conn != null) {
			try {
				conn.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 通用的获得连接的方法
	 * @return
	 * @throws Exception
	 *//*
	//this.getClass().getClassLoader().getResourceAsStream()	//从本工程内寻找资源
	//this.getClass().getResourceAsStream()		//从本包内寻找资源
	public static Connection getConnection() throws Exception {
		Connection conn;
		Properties prop = new Properties();
		InputStream in = DBUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
		prop.load(in);
		
		String driverClass = prop.getProperty("driverClass");
		String url = prop.getProperty("url");
		String user = prop.getProperty("user");
		String password = prop.getProperty("password");
		
		Class.forName(driverClass);
		conn = DriverManager.getConnection(url, user, password);
		Properties info = new Properties();
		info.put("user",user);
		info.put("password", password);
		conn = DriverManager.getConnection(url, info);
		return conn;
	}*/
	
	
	/**
	 * 通用的资源释放方法
	 * @param conn
	 * @param stat
	 * @param rs
	 */
	public static void close(Connection conn, Statement stat, ResultSet rs){
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(stat != null) {
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	
	/**
	 * 以下方法写在Dao层
	 */
	
	/**
	 * 通用的增、删、改方法（存在 SQL注入 危险）
	 * @param sql
	 * @return
	 *//*
	public static int IUD(String sql){
		int count=0;
		Connection conn = null;
		Statement stat = null;
		
		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			count = stat.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, stat, null);
		}
		return count;
	}
	
	
	*//**
	 * 通用的增、删、改方法（防 SQL注入 版）
	 * @param sql
	 * @param args
	 * @return
	 *//*
	public static int IUD(String sql, Object... args){
		int count=0;
		Connection conn = null;
		PreparedStatement stat = null;
		
		try {
			conn = DBUtils.getConnection();
			stat = conn.prepareStatement(sql);
			for(int i=0; i<args.length; i++) {
				stat.setObject(i+1, args[i]);
			}
			count = stat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, stat, null);
		}
		return count;
	}
	
	
	*//**
	 * 通用的 User表查询方法
	 * @param sql
	 * @param args
	 * @return
	 *//*
	public static User getOneUser(String sql, Object... args) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		User user=null;
		
		try {
			conn = DBUtils.getConnection();
			stat = conn.prepareStatement(sql);
			for(int i=0; i<args.length; i++) {
				stat.setObject(i+1, args[i]);
			}
			rs = stat.executeQuery();
			if(rs.next()) {
				user = new User();
				ResultSetMetaData rsmd = rs.getMetaData();
				for(int i=0; i<rsmd.getColumnCount(); i++){
					String key=rsmd.getColumnLabel(i+1);
					Field field = User.class.getDeclaredField(key);
					field.setAccessible(true);
					field.set(user, rs.getObject(key));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(conn, stat, rs);
		}
		
		return user;
	}
	
	
	*//**
	 * 
	 * @param clazz
	 * @param sql
	 * @param args
	 * @return
	 *//*
	public static <T>T getOneData(Class clazz, String sql, Object... args) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		T entity = null;
		
		try {
			conn = DBUtils.getConnection();
			stat = conn.prepareStatement(sql);
			for(int i=0; i<args.length; i++) {
				stat.setObject(i+1, args[i]);
			}
			rs = stat.executeQuery();
			
			if(rs.next()) {
				entity = (T) clazz.getDeclaredConstructor().newInstance();
				
				ResultSetMetaData rsmd = rs.getMetaData();		//获取源数据
				int columnCount = rsmd.getColumnCount();	//获取要查询的列的总列数
				Map<String, Object> map = new HashMap<>();
				
				for(int i=0; i<columnCount; i++){
					String key = rsmd.getColumnLabel(i+1);		//获取该列的列名
					Object val = rs.getObject(key);		//根据列名获取到数据
					map.put(key, val);
				}
				
				for(Entry<String, Object> entry : map.entrySet()) {
					Field field = clazz.getDeclaredField(entry.getKey());
					field.setAccessible(true);
					field.set(entity, entry.getValue());
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(conn, stat, rs);
		}
		
		return entity;
	}*/
	
	
	/*public static void main(String[] args) throws Exception {
		
		System.out.println(DBUtils.getConnection());
		
	}*/
}

